#include "qdatasession.h"

QDataSession::QDataSession(){}

QDataSession::QDataSession(QSqlDatabase connection){
    this->connection = connection;
}

QDataSession::~QDataSession(){
    this->close();
}

bool QDataSession::open(){
    if(!this->connection.isValid()){
        return false;
    }
    if(this->connection.isOpen()){
        return true;
    }
    if(!this->connection.open()){
        if(this->connection.isOpenError()){
            this->print(this->connection.lastError());
        }
        return false;
    }
    return true;
}

bool QDataSession::close(bool commit){
    if(!this->connection.isValid()){
        return true;
    }
    if(!this->connection.isOpen()){
        return true;
    }
    if(commit){
        this->connection.commit();
    }
    this->connection.close();
    return true;
}

bool QDataSession::isOpen(){
    if(!this->connection.isValid()){
        return false;
    }
    return this->connection.isOpen();
}

bool QDataSession::isValid(){
    return this->connection.isValid();
}

bool QDataSession::transaction(){
    if(!this->isOpen()){
        return false;
    }
    return this->connection.transaction();
}

bool QDataSession::rollback(){
    if(!this->isOpen()){
        return false;
    }
    return this->connection.rollback();
}

bool QDataSession::commit(){
    if(!this->isOpen()){
        return false;
    }
    return this->connection.commit();
}

void QDataSession::print(QSqlQuery query){
    qDebug() << "sql" << query.lastQuery();
    auto values = query.boundValues();
    auto i = values.constBegin();
    while (i != values.constEnd()) {
        qDebug() << i.value();
        ++i;
    }
}

void QDataSession::print(QSqlError error){
    qDebug() << "error code" << error.nativeErrorCode() << "message" << error.text();
}

QStringList QDataSession::showTable(){
    if(!this->connection.isValid()){
        return QStringList();
    }
    return this->connection.tables();
}

bool QDataSession::hasTable(QString name){
    auto table = this->showTable();
    return table.indexOf(name) != -1;
}

QDataSet QDataSession::list(QString sql,QVariantList args){
    if(sql.isEmpty()){
        auto message = QString("sql is empty");
        auto error = QSqlError(message,message,QSqlError::StatementError);
        return QDataSet(sql,args,error);
    }
    if(!this->isOpen()){
        this->open();
    }
    if(!this->open()){
        auto message = QString("can not open database connection");
        auto error = QSqlError(message,message,QSqlError::ConnectionError);
        return QDataSet(sql,args,error);
    }
    auto query = this->create();
    query.prepare(sql);
    this->bind(query,args);
    if(!query.exec()){
        return QDataSet(sql,args,query.lastError());
    }
    return QDataSet(query);
}

QDataTable QDataSession::table(QString sql,QVariantList args){
    return this->list(sql,args).first();
}

bool QDataSession::excute(QString sql,QVariantList args){
    if(sql.isEmpty()){
#ifndef QT_NO_DEBUG
        qDebug() << "sql is empty";
#endif
        return false;
    }
    if(!this->isOpen()){
        this->open();
    }
    if(!this->open()){
#ifndef QT_NO_DEBUG
        qDebug() << "can not open database connection";
#endif
        return false;
    }
#ifndef QT_NO_DEBUG
    qDebug() << "sql" << sql;
#endif
    auto query = this->create();
    query.prepare(sql);
    this->bind(query,args);
    if(!query.exec()){
        this->print(query.lastError());
        return false;
    }
    return true;
}

QDataTable QDataSession::table(QString tableName,QString where,QString orderBy){
    if(orderBy.isEmpty()){
        return this->table(tableName,where,QStringList());
    }
    return this->table(tableName,where,QStringList{orderBy});
}

QDataTable QDataSession::table(QString tableName,QString where,QStringList orderBy){
    if(tableName.isEmpty()){
        return QDataTable();
    }
    QString sql = QString("select * from %1").arg(tableName);
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(where);
    }
    if(!orderBy.isEmpty()){
        sql = QString("%1 order by %2").arg(sql).arg(orderBy.join(","));
    }
    sql.append(";");
    return this->list(sql).first();
}

QDataTable QDataSession::table(QString tableName,QVariantMap where,QString orderBy){
    if(orderBy.isEmpty()){
        return this->table(tableName,where,QStringList());
    }
    return this->table(tableName,where,QStringList{orderBy});
}

QDataTable QDataSession::table(QString tableName,QVariantMap where,QStringList orderBy){
    if(tableName.isEmpty()){
        return QDataTable();
    }
    QString sql = QString("select * from %1").arg(tableName);

    QStringList bind = QStringList();
    QVariantList args = QVariantList();

    QStringList filter = where.keys();
    for(int i=0;i<filter.count();i++){
        auto column = filter.at(i);
        bind.append(QString("%1=:%2").arg(column).arg(column));
        args.append(where.value(column));
    }
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(bind.join(" and "));
    }
    if(!orderBy.isEmpty()){
        sql = QString("%1 order by %2").arg(sql).arg(orderBy.join(","));
    }
    sql.append(";");
    return this->list(sql,args).first();
}

QDataTable QDataSession::table(QString tableName,QStringList columns,QString where,QString orderBy){
    if(orderBy.isEmpty()){
        return this->table(tableName,columns,where,QStringList());
    }
    return this->table(tableName,columns,where,QStringList{orderBy});
}

QDataTable QDataSession::table(QString tableName,QStringList columns,QString where,QStringList orderBy){
    if(tableName.isEmpty()){
        return QDataTable();
    }
    if(columns.isEmpty()){
        return QDataTable();
    }
    QString sql = QString("select %1 from %2").arg(columns.join(",")).arg(tableName);
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(where);
    }
    if(!orderBy.isEmpty()){
        sql = QString("%1 order by %2").arg(sql).arg(orderBy.join(","));
    }
    sql.append(";");
    return this->list(sql).first();
}

QDataTable QDataSession::table(QString tableName,QStringList columns,QVariantMap where,QString orderBy){
    if(orderBy.isEmpty()){
        return this->table(tableName,columns,where,QStringList());
    }
    return this->table(tableName,columns,where,QStringList{orderBy});
}

QDataTable QDataSession::table(QString tableName,QStringList columns,QVariantMap where,QStringList orderBy){
    if(tableName.isEmpty()){
        return QDataTable();
    }
    if(columns.isEmpty()){
        return QDataTable();
    }
    QString sql = QString("select %1 from %2").arg(columns.join(",")).arg(tableName);

    QStringList bind = QStringList();
    QVariantList args = QVariantList();

    QStringList filter = where.keys();
    for(int i=0;i<filter.count();i++){
        auto column = filter.at(i);
        bind.append(QString("%1=:%2").arg(column).arg(column));
        args.append(where.value(column));
    }
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(bind.join(" and "));
    }
    if(!orderBy.isEmpty()){
        sql = QString("%1 order by %2").arg(sql).arg(orderBy.join(","));
    }
    sql.append(";");
    return this->list(sql,args).first();
}

bool QDataSession::add(QString tableName,QVariantMap data){
    if(tableName.isEmpty()){
        return false;
    }
    if(data.isEmpty()){
        return false;
    }
    QString sql = QString("insert into %1(%2) values(%3);");

    QStringList bind = QStringList();
    QVariantList args = QVariantList();

    QStringList columns = data.keys();
    for(int i=0;i<columns.count();i++){
        auto column = columns.at(i);
        bind.append(QString(":%1").arg(column));
        args.append(data.value(column));
    }
    sql = sql.arg(tableName).arg(columns.join(",")).arg(bind.join(","));
    return this->excute(sql,args);
}

bool QDataSession::add(QString tableName,QStringList columns,QVariantMap data){
    if(tableName.isEmpty()){
        return false;
    }
    if(columns.isEmpty()){
        return false;
    }
    if(data.isEmpty()){
        return false;
    }
    QString sql = QString("insert into %1(%2) values(%3);");

    QStringList bind = QStringList();
    QVariantList args = QVariantList();

    for(int i=0;i<columns.count();i++){
        auto column = columns.at(i);
        bind.append(QString(":%1").arg(column));
        args.append(data.contains(column)?data.value(column):QVariant());
    }
    sql = sql.arg(tableName).arg(columns.join(",")).arg(bind.join(","));
    return this->excute(sql,args);
}

bool QDataSession::add(QString tableName,QList<QVariantMap> data){
    if(tableName.isEmpty()){
        return false;
    }
    if(data.isEmpty()){
        return false;
    }

    // 是否出错
    auto error = false;
    for(int i=0;i<data.count();i++){
        auto item = data.at(i);
        if(!this->add(tableName,item)){
            error = true;
            break;
        }
    }
    return !error;
}

bool QDataSession::add(QString tableName,QStringList columns,QList<QVariantMap> data){
    if(tableName.isEmpty()){
        return false;
    }
    if(columns.isEmpty()){
        return false;
    }
    if(data.isEmpty()){
        return false;
    }
    QString sql = QString("insert into %1(%2) values %3;");

    QStringList bind = QStringList();
    QVariantList args = QVariantList();

    for(int i=0;i<data.count();i++){
        auto item = data.at(i);
        QStringList temp = QStringList();
        for(int j=0;j<columns.count();j++){
            auto column = columns.at(j);
            temp.append(QString(":%1_%2").arg(column).arg(i));
            args.append(item.contains(column)?item.value(column):QVariant());
        }
        bind.append(QString("(%1)").arg(temp.join(",")));
    }
    sql = sql.arg(tableName).arg(columns.join(",")).arg(bind.join(","));
    return this->excute(sql,args);
}

bool QDataSession::remove(QString tableName,QString where){
    if(tableName.isEmpty()){
        return false;
    }
    QString sql = QString("delete from %1").arg(tableName);
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(where);
    }
    sql.append(";");
    return this->excute(sql);
}

bool QDataSession::remove(QString tableName,QVariantMap where){
    if(tableName.isEmpty()){
        return false;
    }
    QString sql = QString("delete from %1").arg(tableName);

    QStringList bind = QStringList();
    QVariantList args = QVariantList();

    QStringList filter = where.keys();
    for(int i=0;i<filter.count();i++){
        auto column = filter.at(i);
        bind.append(QString("%1=:%2").arg(column).arg(column));
        args.append(where.value(column));
    }
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(bind.join(" and "));
    }
    sql.append(";");
    return this->excute(sql,args);
}

bool QDataSession::update(QString tableName,QVariantMap data,QString where){
    if(tableName.isEmpty()){
        return false;
    }
    if(data.isEmpty()){
        return false;
    }
    QString sql = QString("update %1 set %2");

    QVariantList args = QVariantList();
    QStringList bind = QStringList();

    QStringList columns = data.keys();
    for(int i=0;i<columns.count();i++){
        auto column = columns.at(i);
        bind.append(QString("%1=:%2").arg(column).arg(column));
        args.append(data.value(column));
    }
    sql = sql.arg(tableName).arg(bind.join(","));
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(where);
    }
    sql.append(";");
    return this->excute(sql,args);
}

bool QDataSession::update(QString tableName,QVariantMap data,QVariantMap where){
    if(tableName.isEmpty()){
        return false;
    }
    if(data.isEmpty()){
        return false;
    }
    QString sql = QString("update %1 set %2");

    QVariantList args = QVariantList();
    QStringList bind = QStringList();

    QStringList columns = data.keys();
    for(int i=0;i<columns.count();i++){
        auto column = columns.at(i);
        bind.append(QString("%1=:%2").arg(column).arg(column));
        args.append(data.value(column));
    }
    sql = sql.arg(tableName).arg(bind.join(","));

    bind.clear();
    QStringList filter = where.keys();
    for(int i=0;i<filter.count();i++){
        auto column = filter.at(i);
        bind.append(QString("%1=:%2").arg(column).arg(column));
        args.append(where.value(column));
    }
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(bind.join(" and "));
    }
    sql.append(";");
    return this->excute(sql,args);
}

bool QDataSession::update(QString tableName,QStringList columns,QVariantMap data,QString where){
    if(tableName.isEmpty()){
        return false;
    }
    if(columns.isEmpty()){
        return false;
    }
    if(data.isEmpty()){
        return false;
    }
    QString sql = QString("update %1 set %2");

    QVariantList args = QVariantList();
    QStringList bind = QStringList();

    for(int i=0;i<columns.count();i++){
        auto column = columns.at(i);
        bind.append(QString("%1=:%2").arg(column).arg(column));
        args.append(data.contains(column)?data.value(column):QVariant());
    }
    sql = sql.arg(tableName).arg(bind.join(","));
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(where);
    }
    sql.append(";");
    return this->excute(sql,args);
}

bool QDataSession::update(QString tableName,QStringList columns,QVariantMap data,QVariantMap where){
    if(tableName.isEmpty()){
        return false;
    }
    if(data.isEmpty()){
        return false;
    }
    QString sql = QString("update %1 set %2");

    QVariantList args = QVariantList();
    QStringList bind = QStringList();

    for(int i=0;i<columns.count();i++){
        auto column = columns.at(i);
        bind.append(QString("%1=:%2").arg(column).arg(column));
        args.append(data.contains(column)?data.value(column):QVariant());
    }
    sql = sql.arg(tableName).arg(bind.join(","));

    bind.clear();
    QStringList filter = where.keys();
    for(int i=0;i<filter.count();i++){
        auto column = filter.at(i);
        bind.append(QString("%1=:%2").arg(column).arg(column));
        args.append(where.value(column));
    }
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(bind.join(" and "));
    }
    sql.append(";");
    return this->excute(sql,args);
}

int QDataSession::count(QString tableName,QString where){
    if(tableName.isEmpty()){
        return 0;
    }
    QString sql = QString("select count(1) as count from %1").arg(tableName);
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(where);
    }
    sql.append(";");
    auto data = this->list(sql).first();
    auto scalar = data.scalar();
    if(!scalar.isValid()){
        return 0;
    }
    return scalar.toInt();
}

int QDataSession::count(QString tableName,QVariantMap where){
    if(tableName.isEmpty()){
        return 0;
    }
    QString sql = QString("select count(1) as count from %1").arg(tableName);
    QStringList bind = QStringList();
    QVariantList args = QVariantList();
    QStringList filter = where.keys();
    for(int i=0;i<filter.count();i++){
        auto column = filter.at(i);
        bind.append(QString("%1=:%2").arg(column).arg(column));
        args.append(where.value(column));
    }
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(bind.join(" and "));
    }
    sql.append(";");
    auto data = this->list(sql,args).first();
    auto scalar = data.scalar();
    if(!scalar.isValid()){
        return 0;
    }
    return scalar.toInt();
}

bool QDataSession::exist(QString tableName,QString where){
    return this->count(tableName,where) > 0;
}

bool QDataSession::exist(QString tableName,QVariantMap where){
    return this->count(tableName,where) > 0;
}

QSqlQuery QDataSession::create(){
    return QSqlQuery(this->connection);
}

void QDataSession::bind(QSqlQuery query,QVariantList args){
    if(args.count() == 0){
        return;
    }
    for(int i=0;i<args.count();i++){
        query.bindValue(i,args.at(i));
    }
}

QLimitSession::QLimitSession():QDataSession(){}

QLimitSession::QLimitSession(QSqlDatabase db):QDataSession(db){}

QDataTable QLimitSession::limit(QString tableName,QString where,QString orderBy,QString limit){
    if(orderBy.isEmpty()){
        return this->limit(tableName,where,QStringList(),limit);
    }
    return this->limit(tableName,where,QStringList{orderBy},limit);
}

QDataTable QLimitSession::limit(QString tableName,QString where,QStringList orderBy,QString limit){
    if(tableName.isEmpty()){
        return QDataTable();
    }
    QString sql = QString("select * from %1").arg(tableName);
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(where);
    }
    if(!orderBy.isEmpty()){
        sql = QString("%1 order by %2").arg(sql).arg(orderBy.join(","));
    }
    if(!limit.isEmpty()){
        sql = QString("%1 limit %2").arg(sql).arg(limit);
    }
    sql.append(";");
    return this->list(sql).first();
}

QDataTable QLimitSession::limit(QString tableName,QVariantMap where,QString orderBy,QString limit){
    if(orderBy.isEmpty()){
        return this->limit(tableName,where,QStringList(),limit);
    }
    return this->limit(tableName,where,QStringList{orderBy},limit);
}

QDataTable QLimitSession::limit(QString tableName,QVariantMap where,QStringList orderBy,QString limit){
    if(tableName.isEmpty()){
        return QDataTable();
    }
    QString sql = QString("select * from %1").arg(tableName);

    QStringList bind = QStringList();
    QVariantList args = QVariantList();

    QStringList filter = where.keys();
    for(int i=0;i<filter.count();i++){
        auto column = filter.at(i);
        bind.append(QString("%1=:%2").arg(column).arg(column));
        args.append(where.value(column));
    }
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(bind.join(" and "));
    }
    if(!orderBy.isEmpty()){
        sql = QString("%1 order by %2").arg(sql).arg(orderBy.join(","));
    }
    if(!limit.isEmpty()){
        sql = QString("%1 limit %2").arg(sql).arg(limit);
    }
    sql.append(";");
    return this->list(sql,args).first();
}

QDataTable QLimitSession::limit(QString tableName,QStringList columns,QString where,QString orderBy,QString limit){
    if(orderBy.isEmpty()){
        return this->limit(tableName,columns,where,QStringList(),limit);
    }
    return this->limit(tableName,columns,where,QStringList{orderBy},limit);
}

QDataTable QLimitSession::limit(QString tableName,QStringList columns,QString where,QStringList orderBy,QString limit){
    if(tableName.isEmpty()){
        return QDataTable();
    }
    if(columns.isEmpty()){
        return QDataTable();
    }
    QString sql = QString("select %1 from %2").arg(columns.join(",")).arg(tableName);
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(where);
    }
    if(!orderBy.isEmpty()){
        sql = QString("%1 order by %2").arg(sql).arg(orderBy.join(","));
    }
    if(!limit.isEmpty()){
        sql = QString("%1 limit %2").arg(sql).arg(limit);
    }
    sql.append(";");
    return this->list(sql).first();
}

QDataTable QLimitSession::limit(QString tableName,QStringList columns,QVariantMap where,QString orderBy,QString limit){
    if(orderBy.isEmpty()){
        return this->limit(tableName,columns,where,QStringList(),limit);
    }
    return this->limit(tableName,columns,where,QStringList{orderBy},limit);
}

QDataTable QLimitSession::limit(QString tableName,QStringList columns,QVariantMap where,QStringList orderBy,QString limit){
    if(tableName.isEmpty()){
        return QDataTable();
    }
    if(columns.isEmpty()){
        return QDataTable();
    }
    QString sql = QString("select %1 from %2").arg(columns.join(",")).arg(tableName);

    QStringList bind = QStringList();
    QVariantList args = QVariantList();

    QStringList filter = where.keys();
    for(int i=0;i<filter.count();i++){
        auto column = filter.at(i);
        bind.append(QString("%1=:%2").arg(column).arg(column));
        args.append(where.value(column));
    }
    if(!where.isEmpty()){
        sql = QString("%1 where %2").arg(sql).arg(bind.join(" and "));
    }
    if(!orderBy.isEmpty()){
        sql = QString("%1 order by %2").arg(sql).arg(orderBy.join(","));
    }
    if(!limit.isEmpty()){
        sql = QString("%1 limit %2").arg(sql).arg(limit);
    }
    sql.append(";");
    return this->list(sql,args).first();
}
